package com.atguigu.gulimall.product.util;

import com.atguigu.gulimall.product.eo.AttrGroupEo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ImportExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ImportExcelUtil.class);
    private final Workbook wb;
    private HSSFFormulaEvaluator hssEvaluator;
    private XSSFFormulaEvaluator xssEvaluator;
    private final Sheet sheet;
    private final int headerIndex;

    public ImportExcelUtil(String fileName, int headerIndex) throws IOException {
        this(new File(fileName), headerIndex);
    }

    public ImportExcelUtil(File file, int headerIndex) throws IOException {
        this((File)file, headerIndex, 0);
    }

    public ImportExcelUtil(String fileName, int headerIndex, int sheetIndex) throws IOException {
        this(new File(fileName), headerIndex, sheetIndex);
    }

    public ImportExcelUtil(File file, int headerIndex, int sheetIndex) throws IOException {
        this(file.getName(), new FileInputStream(file), headerIndex, sheetIndex);
    }

    public ImportExcelUtil(MultipartFile multipartFile, int headerIndex, int sheetIndex) throws IOException {
        this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerIndex, sheetIndex);
    }

    /**
     *
     * @param fileName      文件名
     * @param is            文件流
     * @param headerIndex   TODO excel头格索引位
     * @param sheetIndex    sheet页索引位
     * @throws IOException
     */
    public ImportExcelUtil(String fileName, InputStream is, int headerIndex, int sheetIndex) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            throw new RuntimeException("导入文档为空!");
        } else {
            if (fileName.toLowerCase().endsWith("xls")) {
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                this.wb = hssfWorkbook;
                this.hssEvaluator = new HSSFFormulaEvaluator(hssfWorkbook);
            } else {
                if (!fileName.toLowerCase().endsWith("xlsx")) {
                    throw new RuntimeException("文档格式不正确!");
                }

                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
                this.wb = xssfWorkbook;
                this.xssEvaluator = new XSSFFormulaEvaluator(xssfWorkbook);
            }

            if (this.wb.getNumberOfSheets() < sheetIndex) {
                throw new RuntimeException("文档中没有工作表!");
            } else {
                this.sheet = this.wb.getSheetAt(sheetIndex);
                this.headerIndex = headerIndex;
                log.debug("Initialize success.");
            }
        }
    }

    public Workbook getWb() {
        return this.wb;
    }

    public Row getRow(int rowNum) {
        return this.sheet.getRow(rowNum);
    }

    public int getFirstDataRowIndex() {
        return this.headerIndex + 1;
    }

    public int getLastDataRowNum() {
        return this.sheet.getLastRowNum();
    }

    public int getLastCellNum() {
        return this.getRow(this.headerIndex).getLastCellNum();
    }

    public Object getCellValue(Row row, int column) {
        Object val = "";

        try {
            Cell cell = row.getCell(column);
            if (cell != null) {
                CellValue tempCellValue;
                if (this.hssEvaluator != null) {
                    HSSFCell hssfCell = (HSSFCell)cell;
                    if (hssfCell.getCellType() == CellType.FORMULA) {
                        tempCellValue = this.hssEvaluator.evaluate(hssfCell);
                        return tempCellValue.getNumberValue();
                    }
                } else {
                    XSSFCell xssfCell = (XSSFCell)cell;
                    if (xssfCell.getCellType() == CellType.FORMULA) {
                        tempCellValue = this.xssEvaluator.evaluate(xssfCell);
                        return tempCellValue.getNumberValue();
                    }
                }

                if (cell.getCellType() == CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                } else if (cell.getCellType() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }

            return val;
        } catch (Exception var7) {
            return val;
        }
    }

    public List<AttrGroupEo> getDataList(List<AttrGroupEo> dataList) {
        int rowLen = this.getLastDataRowNum()+1;//最后一行
        //获取数据
        for(int i = 1;i < rowLen;i++){
            AttrGroupEo eo = new AttrGroupEo();
            Object catelogName = this.getCellValue(this.getRow(i),0);
            eo.setCatelogName(catelogName.toString());
            Object attrGroupName = this.getCellValue(this.getRow(i),1);
            eo.setAttrGroupName(attrGroupName.toString());
            Object descript = this.getCellValue(this.getRow(i),2);
            eo.setDescript(descript.toString());
            Object icon = this.getCellValue(this.getRow(i),3);
            eo.setIcon(icon.toString());
            Object sort = this.getCellValue(this.getRow(i),4);
            String[] split = sort.toString().split("\\.");
            eo.setSort(Integer.valueOf(split[0]));
            dataList.add(eo);
        }
        return dataList;
    }

    public static void main(String[] args) throws IOException {
        String filePath = "D:/属性分组.xlsx";
        ImportExcelUtil importUtil = new ImportExcelUtil(filePath,0);
        List<AttrGroupEo> attrGroupEos = new ArrayList<>();
        attrGroupEos = importUtil.getDataList(attrGroupEos);
        attrGroupEos.forEach(item -> {
            System.out.println(item.toString());
        });

    }
}
